1
2
  -2021
/ 5 
  
(      )
   : 3 .  :75
:                  
     
 -
1.(a)           8
()          
2.        :
()  
()     
 -
3.    ?          15
4.       :
()   
()    .
     - www.onlinedoctranslator.com
2
2
 -
5. DBMS       Oracle 10g        

6. SQL           DDL  DML      

 -
7.(a)     ?          
 
(b)                
         
8. PL/SQL        :
()   
()  .
3
2
  -2021
/ 5 
  
(      )
   : 3 .  :75
:                   
     
 -
1.(a)           
:      (DBMS)  
   (DBMS)           ,
               
                   
  ,              
      DBMS  ,        
      
     DBMS    ,       :
1.   
  DBMS         ,  ,  
             
  ""               
           ,   ,  
       
4
2
         :
  :               
  ,           
 
 : SQL (  )       
        
  -   :       (   
) , ,    (ACID)      
     
2.   
               ,
 , ,              
               
  ,   ,         ,  
ISBN    " "        ,  
      ""         
         ,   ,      
     
       :
  :             
  
   :            
,    
 :           
3.   
              DBMS  
    ,           
     :
 :            
   
5
2
 :          
  ,              
     
  :            
       
  ,                
      ,          
4.  
            (  SQL   )
                  
    :
  :               
     
  :          ,  
                 
  :             
   
  ,   DBMS            , 
         ,       
 ,       
5.   
         ,     
 -  :
    ()):             
    ,       ,  
            
  :            
   DBMS           
       
         ,       
             
   :
6
2
o   :   -    
     
o   :        
       
6. 
 ,          ,       
     ,            
, ,           DBMS  
                   
    
   ,   " "          ,
    ( ,  , )       (  
  ISBN   )   
7.   
                
      ,     ,       
              
                 
  
8.   
      DBMS       
   ,            
      (GUI)         
        ,   -  (CLI) 
    
        GUI         
    ,           
           ,    
      
9.  
  DBMS     ,        
           ,  
          :
7
2
   :         
  
 :          
        ,    
    ,        
:            
   
  ,           ,  
               
,            
  ,          ,   
             
  ,      ,     
      ,           
    ,      
()          
:   (DBMS)         
               
              
  
1.    ?
     ,         
     ,             
   -     ,    
     ,                
2.      ?
     ,       ,     
           ,   :
   :        ,    
    ,      
8
2
   :           
    
               
              
3.       ()
  " "             
,             :
     (1NF)
  :  1NF        ()   ,
         
 :             
        ,   1NF    1NF   
,           
1NF  :
   
 

12345, 67890
1NF   :
   
 

12345

67890
:           -  
          
     (2NF)
  :      1NF     -   
       ,   2NF    ,      
        
 :         (     ) 
 ,           2NF ,    
     
9
2
2NF  :
  
  
  
1
101

2NF   :
o    :
  
101
o     :
  
  
101

:2NF            ,  
         ,      
     (3NF)
  :   2NF         ,   3NF 
         -      

 :              , 
        
3NF  :
  
 
  
101
1
 
10
2
3NF   :
o     :
 
 
101
1
o    :
 
 
1

: 3NF           -   
      ,  -    
-     ()
  :BCNF, 3NF         3NF  ,   BCNF 
,     (       )  ,
      (           )
 
 :       3     
    ,         
     (4NF)
  :   BCNF      -   , 
4NF    -           
  
 :       ,      
  ,  -     4NF    -
     
4NF  :



1

. 
1

. 
11
2
4NF   :
o  -    :


1

1

o  -   :



. 

. 
:4NF -     ,       
         
5      (5NF)
  :    4NF            
 ,   5NF     5NF         
    ,          
  
 :           ,  5NF 
               
4.      
       ,    :
   -     ,   
            ,   
 
           , 
           ,  
    
12
2
 :         
,   ,     
    ,      
 ,            
5.      
     ,     :
           , 
     
  :   ,       
      ,       ,   
 
    :        
      
6.   
-,           
                 
             ,   
           ,    
                 
7.  
      
-  -  , , ,      
               
      -    ,    
    
       ,     
,        -   
                 
      
   ,         , 
    -        
           
13
2

              
  ,            
           ,   
    ,           
         ,   
         
2.        :
()  
:    
  
     (DBMS)       
              
                
                  
         
      ,           
 (    )   (    )   
                   
  
  ,           
 ()       ,          
          
1.      
              
             :
14
2
1.  (σ)
2.  (π)
3.  ()
4.   (−)
5.   (×)
6.   (ρ)
1.1.  (σ)
             
     
 :     'StudentID', 'Name'  'Age'   
Students       18         
 ,   Age > 18            
   
    ,      :
: σ     ,    > 18      
            18    

1.2.  (π)
:         ( )  
    
            , 
             
    ,      :
: π     ,          
           
15
2
1.3.  ()
:           , 
    
 :       , StudentsInCourseA 
StudentsInCourseB,       StudentID  Name  
         ,        
    
    ,     :
:     ,       ,  
 ,             
1.4.   (−)
:               

 :         StudentsInCourseA   
StudentsInCourseB   ,       
    ,     :
:      ,        
   StudentsInCourseA  ,  StudentsInCourseB  
1.5.    (×)
:  ,         
         
 :           
          ,   
    
16
2
    ,      :
:×      ,       
          ,    
         
1.6.    (ρ)
:               
   
 :  Students     EnrolledStudents   ,
  rename    
    ,     :
:ρ       ,       
 EnrolledStudents           
              
2.      
   ,          
                
 
1. 
2. 
3. 
2.1. 
:            

   :
o  :          .
17
2
o  :           
o     :            
  
:       ''      ,
     ' '    ' '    
        ,     
 
    ,      :
:     ,        
      StudentID   
2.2.  (∩)
             
 :     , StudentsInCourseA  StudentsInCourseB,
             ,   
   
    ,     :
: ∩     ,        
    StudentsInCourseA  StudentsInCourseB    
2.3.  
:             
              
             
             ,   
   
18
2
    ,      :
:÷     ,         
     CoursesCompleted       

3.     
         DBMS      
  SQL (  )      ,  DBMS    
               
               
   DBMS              ,
 DBMS             
4.   
             
  ,             
,      ,          
 
        :
:       18         
  
:
1. 18              
 
2. StudentID            
    
3.              
    ,      :
19
2
7.   
             
                
   , , ,  ,       
 SQL        ,        
         
        ,       
   ,            
    -  DBMS     ,   
                
  ,               

       DBMS        
 
()     
:      
     (DBMS)          
    ,       
1.  ?
           ,    
                  
     ,            
    ,     ,    - 
    
2.   
  1960              
                
  ,           ,      

  ,           ,   (
)   ,        ( )    
20
2
            ,    
   
   ,             
      ,   --  (   
   )      ,    -- 
(      )    
3.      
                
        :
1.      :         
      -     ,     
        ,       
               
       ,     , 
       ,       
     --    
2.       ,       
  ,            
      ,            
             
     
3.     :        ,  
             , 
               
            ,  
        
21
2
4. --    :       
  --           ,
   ,        ,   
               
       ,      
5.   :           
                
          ,    
             
       ,       
  
6.         :  ,   
      ,         
           ,         
        ,     , 
              
              ,
      
7.       :       
          -    
 ,    ,         
 ,             
  
4.    
  
                
  :
            ,     
(,  )      ,         
,             --    

  ,     ,    -- 
    (  ,         ) , 
 ,  ,              
              
     (  ,       )   
22
2
5.       
          ,      
         ()    ()  ,  
  -     ,    ,   
         ,      
:      ,   ,   
               
  :            
        ,   ,   
        ,        

   :   ,     ,    
 ,   ,         ,  
               
   
6.   
      ,            
        :
1.  :  --        
        
2.            ,   
      
3.   :          ,   
               

4.  :           
 ,                
     
7.     
   ,         DBMS   
   :
1.  :           
             
      ,        
23
2
2.    :   ,     (SQL)
 ,              
   ,       SQL-   
     
3.   :            ,
            DBMS 
    ,         
    
8. 
  DBMS     ,        
          --   
  ,             
       ,    
         ,      
   
 ,              
                
                
         ,       
  
 -
3.    ?         
:        
     (DBMS)         ,   
                 
         DBMS        
                
 
24
2
1.  ?
                  
                  
                 ,    
           ,       
            ,       DBMS 
    
2.       ?
          ,       
                   
                
     ,          
         
    ,      :
1.   :           ,    
   
2.   :               
 (  )           , 
       
3.                
,        
4.   :            
,        
 ,              , 
        
3.       ?
      DBMS        :
1. 
2.  
3.   
  ,      ,       
     
25
2
     
          - -    
  "  "              , 
     , ",              
   "
            ,     
  ,   ,       ,   DBMS  
      
1.     
     ,           :
1.    ( ):             
                  
  ,               
  
2.   ( ):           
                
 ;               
3.  :             
     (          )  
                  
4.  :             
                  
                
        ,         
  
2.       
      DBMS       :
    :             , 
  (  )         DBMS  
                
  
           ,       
      (  )  
26
2
  :-       ,      ,
  -            
  -    (   )        
()
       ,        ,    
          ,         
 - ,      
3.  
                 
             ,  -
      :
1. -  (2PL):           
        :
o   :        ,   
     
o   :         ,    
    
 -                ,
                 
        
2.  -  :  -        
 ,                
                
      
3.    :         - -
                
 ,    -         , DBMS
-     :
o   :    -     
    ,       
o             
        ,        
    
27
2
     
        ,      
:
 :
 :               
  
 :     ,         
             
 :
 :           ,
            
:         ,   - 
    ,     
     :           
 ,         

         ,      
            -     
                 
   ,            ,
                  

               
                
             
     
28
2
4.       :
()   
()    .
: ()   
          ,     
  ,                 
           :
1.      ?
   :    ,    
           
   ,     ,        
              
      ,    ,    
           ,     
   
              ,    
    ,         
2.         
         :
 :             
 ,           
 :           URL 
 SQL    ,         
               
,     
 : -          
              
  
   :    ,     
             
29
2
3.     
             :
 :          
               
           :
o    :          
  ,    ,     
     
o  -    ():   
           
  ,       ,  
  ,     
              
                  ,
         
            (): 
            ,  
           
             
               
  
         ,   
                
4.      
       ,       
 :
    :          
    
 :             
        
     :      , 
             
30
2
     :        
              
    
              
 ,   ,      ,     
    
()    
               
    ,       ,  
        -     ,    
1.     ?
       (AI)          
                
                
       ,          
 
2.         ?
          :
  :           ,  
               , 
   ,     , ,   
    
  :                
                
            
            (    
    ),             
  
3.      
      ,     -     
   :
-      "-"       
  ,        ,     
31
2
     -  -  ,    
     
  :-      "  "  
 ,            
, "    "   ,       "
     "
 -               
              
         
4.     
              :
            
               
           
  ,       ,  
               
:    ,     , 
            
   
         ,   
             
5.      
        :
 :   ,        
 ,    ,      
 :  24x7           
     ,        
      
             ,  
              
 
32
2
             ,   
       ,        
6.      
   ,      :
   :    -    
                 
    
    :        
              , 
     
            
            
,           
7.    
-     ,          
                 
        ,       
 ,                 
 
               
        ,  ,   -
            
 -
5. DBMS       Oracle 10g        

: DBMS    Oracle 10g  -  :  
Oracle 10g Oracle    (DBMS)       
-           
33
2
          ,       
   ,  Oracle 10g   -   
         
1. -   () 
Oracle 10g       ,      -
 (OOP)    OOP          
  ""  -       OOP   
  :
 :           ()  
 
 :              
 :             ,
      
:        
 10          ,   
          
2.   
Oracle 10g              
  -           
     , OOP        ( ) 
 (     )    
 :         ,   Student 
    ,  ,      
calculateGPA()   
              , 
      
3.  
Oracle 10g ,             
    ,      ,      
           
 :      ,     
            
      
34
2
           ,      
   
4.      
Oracle 10g     ,         
                
    
 :        Person    
   ,   Student      Person   
       calculateGPA()   
       ,      
          
5. 
Oracle 10g     ,         
    -             
               
 
 :         displayInfo()   
     Student ,  Person     ,  
        displayInfo()    
               
 
6.  
  Oracle 10g            
           ,     
      -       
 :     ,      -
  ,             
           ,   
     -    
            -  
      
35
2
7.  
-    , Oracle 10g       
                 

 :    ,  calculateGPA()      
              
       ,       
   
8. 
Oracle 10g          (REF)    
         ,       
     
 :          ,   
                
    
                ,
     OOP    
9.    VARRAYs
Oracle 10g   
   VARRAYs,            
          --     
         
 :               
        (,      )
  
        ,       
  
   VARRAY             
   ,         
10.    
Oracle 10g  -    ,      
-             
         
36
2
 :             
      -    
         ,     
         
             
  
11. 
Oracle 10g     ,     - 
       -         
 
     , Oracle 10g        
    ,            
                  
  
12.   
Oracle 10g  (AQ)    ,      
         -      
  AQ           ,       
             
   ,      ,     
                
          
        ,        
    
13.  
Oracle 10g      ,        
               -  -
              
 :               
          ,      
    ,         
              
 ,            
37
2
14.  -  
Oracle 10g ,      -    
               
            
 :   ,         
   ,          
      
            ,  
      
15.   
Oracle 10g             , 
 (=)   (!=)           
         
 :   ,         
                
     
     ,          
     ,          
16.    
Oracle 10g  -           
          ,   
                 
  -         
         
 :   ,  ,       
            ,  
          
              
17.      : 
Oracle 10g  -          
           ,     
              
           ,     
  
38
2
 :               
 ,    Student   calculateGPA()    
           ,  
  -       
         ,       
   
18. 
 , Oracle 10g  -       
             , ,
        ,     
              
 ,     ,        
               
   
Oracle 10g          ,  -  
     -       
             ,    
    ,        , Oracle 10g  -
           
6. SQL           DDL  DML      

: SQL   DDL  DML     
SQL (  ) ,             
 : DDL (  )  DML (  )    
        ,       
           SQL     
 (  ) ?
DDL                
    DDL   ,     ,  
       DDL           :
39
2
1.   :              
 ,               
 ,   CREATE    
:
:  SQL  Students     ,  
 : StudentID, FirstName, LastName,  Age.
2. : ALTER           
,   Students         ,   ALTER  
 
:
:   Students   'Gender'      
3. DROP:     ,       DROP  
              
 :
:         
4. :TRUNCATE              
                 
       
40
2
 :
o :          
   
 (  ) ?
DML                  
   DDL        ,      ,
 ,       DML      DML
   :
1.   :          , Students   
   ,  INSERT    
 :
:      Students      

2.  : SELECT         Students   
     ,   SELECT   
 :
:  Students     (*)     
3. :           ,  
      ,   UPDATE   
41
2
 :
:  StudentID = 1      21   
4.   :                , 
 DELETE   
 :
o :  StudentID = 1       
SQL      
SQL           SQL     DDL  DML
      ,         :
 1:     (DDL - CREATE)
 ,             
 ,        :
:            ,
  ,  ,  ,     
 2:  (DML - INSERT)
      ,        :
42
2
:            
 3:    (DML - SELECT)
,            :
:        ,    
  
 4:   (DML - )
          ,     
 :
:      65,000   
 5:   (DML - DELETE)
       ,      :
:          
 6:     (DDL - ALTER)
 ,               :
43
2
:        HireDate  
 7:  (DDL - DROP)
 ,         ,      :
:            

DDL  DML SQL               
DDL  ( CREATE, ALTER,  DROP)        
      ,  DML  ( INSERT, SELECT, UPDATE, 
DELETE)               
SQL        ,       , 
                  DDL
 DML         ,     
              
   ,   SQL            
          
 -
7.(a)     ?          
 
:         
   ,              
                   
44
2
              ,    
  ,   ,          

   
            :     
1.  :
o    SQL  ( SELECT, INSERT, UPDATE,  DELETE) 
  ,           
o             
 ,           
o               
    
2.  :
o              
              
       
o         ,     
  ,     
o              
            
   
 :
              
  ?
   SQL    ,  Oracle      
       PL/SQL         
        ,       
        ,       
    
       ?
           :
45
2
1. SELECT INTO :
o             SELECT INTO  
  ,           
2.  :
o INSERT, UPDATE,  DELETE    (DML)  
,            
   
     :   
          ,    :
1. SQL   :
o   SQL  ( SELECT, INSERT, UPDATE,  DELETE) PL/SQL 
   ,  Oracle      
o   ,   employees        
SELECT INTO   ,      
2.   :
o               
       ,        

3.     :
o             -
      ,         

4.    :
o     ,           
 ,       ,     
     
 :SELECT INTO       
        SELECT INTO       

         _, _, _
             _ 101
46
2
           PL/SQL   :
:
SELECT INTO  employee_id = 101      
           
     ,    ,        

  v_salary      ,   DBMS_OUTPUT.PUT_LINE 
    
  
Oracle               
             SQL%    
  :
1. %ROWCOUNT:
o SQL       
o  : UPDATE  5     , 
SQL%ROWCOUNT 5 
2. %   :
o  SQL            TRUE  ,
 FALSE  
o  : SELECT INTO       , 
SQL%FOUND  
47
2
3. % :
o  SQL          TRUE  , 
FALSE 
o  : DELETE          , 
SQL%NOTFOUND  
4. %:
o      FALSE        
     
o  :        ,  
   SQL%ISOPEN     
 :     
              :
:
SELECT INTO   ,        SQL%FOUND  
    
 SQL%FOUND  ,      ,     
    
%ROWCOUNT           
 ,     1       0 
48
2
    
1.  :
o              
  ,         
2.   :
o    ,           ,
      
3. :
o    -       ,  
         
    
1.  :
o             
        ,      
    
2.   :
o             
     ,     

  ,  Oracle   -       
             ,  
     ,  SELECT INTO        
      ,           
            
              ,  
       PL/SQL    
49
2
(b)                
         
:             
 ,                
  -        ,    Oracle  PL/SQL
( /  ) ,         , 
  ,    ,          
:      ,          
 ?
 PL/SQL                
                 , 
           ,       
     ,   -      
    ?
1.    :        ,     
       ,    
2.             ,  
        -   
3.  :     ,      
  ,       
4.  :          ,  
                
 
PL/SQL    
       :
1. :           (  )  
               
 
2.   :     ,     / 

3.     :            
  SELECT, INSERT, UPDATE,  DELETE SQL    
50
2
4.    :           
      ,          
      
5. :      
            
   :   
       
1. 
  :               
      ,         
  : ,   update_employee_salary    
 ,        
 :     :
o emp_id (   ):         
 
51
2
o new_salary (  NUMBER):        
IN          ,       
      
2.   
  ,       ,        
      ,           

3.     
  :         
 :      SQL UPDATE    employee_id 
  employees         
:     UPDATE        
   
4.    
 :               
     :        UPDATE    ID 
         , DBMS_OUTPUT.PUT_LINE 
      
:   -          
52
2
5. 
:        
   
   ,        :
  101     50,000    
      
1.              
 ,         ;    
  
2.  :            
 ,             , 
         ,      
    emp_id  new_salary     
3. ...:            
BEGIN  END                

4.  :  PL/SQL          
           ,   
           (
NO_DATA_FOUND)           
      
5.  , COMMIT          
        ,       
 ,              
,   - ,        COMMIT 
     
53
2
    
             :
1. OUT        : -,        
        ,  OUT    
 
, emp_salary  OUT  ,         emp_id  
     
2.  SQL    :      SQL      
   :
1.            ,   
       
54
2

PL/SQL      :         , 
   ,           ,
   ,         
       , SQL      
    ,          
  ,              
      ,         
  ,    ,         
      -        ,  
             
8. PL/SQL        :
()   
()  .
: PL/SQL    :      
PL/SQL 
PL/SQL (SQL    ) Oracle       SQL  
  SQL            ,
               PL/SQL
          ,      
      
()   
1.    ?
               
            
  ,          ,    
 ,    ,              
55
2
                 
     
2.      ?
     :
  :           
 
  :       ,      
 
               
,            
3.      ?
              :
  :            .
:         .
  :            .
                 , 
        (BEFORE INSERT ),       
      (AFTER UPDATE )
4.     
               
         ,        
                  
      :
   :
  before_salary_update   .
56
2
                
           salary_audit  
 
5.    
     :
- :            
   ,  10     ,   10   
- :   SQL         , 
       ,   10     ,
      
()  
1.   ?
PL/SQL ,          (SQL      
 )                 
      PL/SQL         
        -         
             ,    
       ,  PL/SQL          
 SELECT         
2.    ?
      :
           -   

          ,   , 
      
              

3.       ?
             :
1.   :         SELECT     
 
57
2
2.  :             
    
3.     : FETCH        - 
  
4.   :      ,       
    
4.    
                 
          PL/SQL    :
   :
 emp_cursor             
   ,    10
   ,       emp_name     
    
                
(emp_cursor%NOTFOUND)
 ,   
5.    
                 
    :
% :         TRUE  .
58
2
% :        TRUE  .
%   :       .
% :       TRUE  .
6.   
-- :          
    ,           
     
  :            , 
   ,          
7.     
                 
-     ,         
              ,  
       

     PL/SQL          
             , 
              
          ,      
   
             
          Oracle     
     ,          
      
:              ( )       
         ,              